You've done the hard work of building your spreadsheet. Now you just need to go through your page setup to ensure it looks great when printed. 

All the little things like what you want to see in your headers and footer, the size or your margins and which orientation works best (portrait or landscape).

Plus, how do you ensure that the pages don’t break in weird places?  

And how do you make headings and labels appear automatically on each page without lots of copy and paste?

Read on, my friend. All these questions are answered below.

1.  Click the Page Layout tab.

2.  Click the Margins button.

2.  Choose from one of the pre-set margin configurations – Normal, Wide or Narrow.

4.  Alternatively, click the Custom Margins link. This opens up the dialog box that appeared in previous version of Excel and allows you to set your own margins.

5.  Set the margins by clicking the spinner boxes or typing values directly into the text boxes.

To align the worksheet contents to the centre of the page vertically and/or horizontally:

1.  Click the Page Layout tab.

2.  Click the Margins button.

3.  Choose Custom Margins.

4.  Tick the boxes labelled Horizontally or Vertically, as appropriate.

PORTRAIT orientation is where the page sits upright with the short side on the bottom, like a model posing for a portrait.

LANDSCAPE orientation is where the page sits flat with the long side on the bottom, like a landscape painting

To set the orientation:

1.  Click the Page Layout tab.

2.  Click the Orientation button.

3.  Choose Portrait or Landscape.

A header and footer are set up once. It then appears on every page that is printed. Typically, headers and footer show information like the author or company name, the filename, dates etc.

1.  Click the Insert tab.

2.  Click the Header & Footer icon

(Note: If you are using a small screen, like on a laptop and you cannot see the icon, look for the Text group-icon, then click the drop-down arrow and you will see the group expanded and the Header & Footer is one of the options.

3.  Type your header text into the header box on the worksheet. You can utilise the icons on the Header & Footer Tools Design ribbon.

1.  Click inside the header or footer text box on the worksheet. This activates the Header & Footer Tools ribbon.

2.  Click one of the icons on the ribbon to insert a placeholder.

For example, if you click the File Name icon, Excel inserts a &[File] placeholder. When you print your worksheet, Excel interprets this into the file name of the workbook.

If you do not feel confident inserting your own header and footer elements, you can use one of the pre-set entries that combines elements such as Page x or y and Author, Page Number and Date.

1.  Click the Insert tab.

2.  Click the Header & Footer icon.

3.  On the Design tab (under Header & Footer Tools), click the Header icon or the Footer icon.

4.  Select from the pre-set entries listed.

Where there are several items separated by commas, Excel places the first item on the left of the page, the second in the centre and the third on the right.

If you have a table of data big enough to spill onto two or more pages, you'll already know that your column headings and row labels do not automatically print on every page.

So, many parts of your printed spreadsheet have no reference point because the labels are missing.

Some people resort to lots of inserting, copying and pasting, but when extra data gets added or existing data is removed, the headers and labels have to be re-positioned again.

Other people just print as-is then cut it up with scissors and carefully tape it together on the wall in a Frankensheet approach.

Here is a better way:

1.  Select the Layout tab (formerly the Page Layout tab).

2.  Click the Print Titles icon. The Sheet tab within the Page Setup box is displayed.

3.  Place the cursor in the Rows to repeat at top box

4.  Move the mouse pointer into the worksheet (it will become ablack horizontal arrow).

5.  Click any row (or rows) that you want to display on every page. Excel will add some funny notation into your dialog box, something like $3:$3. This just means the headings on row 3 will appear where appropriate on every printed page. Neat huh?

The dollar signs apply something call absolute referencing (which just means 'fixed').  

6.  Place the cursor in the Columns to repeat at left box.

7.  Move the mouse pointer into the worksheet (it will become ablack vertical arrow).

8.  Click any column (or columns) that you wish to display on every page. Again, Excel will add some funny notation into your dialog box, something like $A:$A, which means the labels in column A will appear where appropriate on every printed page. 

Next time you check the Print Preview, you’ll see that every page has column headings and row labels.

1.  Select the View tab.

2.  Click the Page Break Preview button.

Page breaks are shown by a thick blue dashed line.

The outer boundary of the printable area is shown by a thick blue solid line.

To move the page break:

1.  Position the mouse pointer over the blue dashed line.

2.  Click and drag the break to a new position.

Excel will expand or reduce the content to fit the page accordingly. For example, if you move a page break to include more columns, Excel reduces the size of the content, so it can still fit onto the page when printed.

You can check what your spreadsheet will look like before you print it.

1.  Click the File tab.

2.  Select the Print option on the left sidebar.

A preview of your page is shown in the main window,

3.  To browse each page, use the navigation buttons underneath the preview image. This  has been circled on the following screen capture.

To control what is printed:

1.  Click the File tab.

2.  Select Print on the left sidebar.

3.  In the area under Settings, choose Print Active Sheets, Print Active Sheets, Print Entire Workbook or Print Selection.

4.  To print a selection of pages, choose Print Selection, then specify the page or pages you would like to print.

5.  In the Copies section, specify how many copies you wish to print.

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Share your biggest win from this page in the comments below.Enjoy the rest of your day!

About the author

Jason Morrell

Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE